<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us">
<head>
  <link href="https://gmpg.org/xfn/11" rel="profile">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta name="generator" content="Hugo 0.68.3" />

  
  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>第七篇文章 &middot; 我的博客</title>
  <meta name="description" content="" />

  
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/print.css" media="print">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/poole.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/syntax.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/hyde.css">
    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Abril+Fatface|PT+Sans:400,400i,700">


  
  <link rel="apple-touch-icon-precomposed" sizes="144x144" href="/apple-touch-icon-144-precomposed.png">
  <link rel="shortcut icon" href="/favicon.png">

  
  
</head>

  <body class="theme-base-0self ">
  <aside class="sidebar">
  <div class="container sidebar-sticky">
    <div class="sidebar-about">
      <a href="/my_technology_blog/"><h1>我的博客</h1></a>
      <p class="lead">
       这里是田帅康的个人博客 
      </p>
    </div>

    <nav>
      <ul class="sidebar-nav">
        <li><a href="/my_technology_blog/">Home</a> </li>
        
      </ul>
    </nav>

    <p>&copy; 2020. All rights reserved. </p>
  </div>
</aside>

    <main class="content container">
    <div class="post">
  <h1>第七篇文章</h1>
  <time datetime=2020-04-11T00:00:00Z class="post-date">Sat, Apr 11, 2020</time>
  <h2 id="python操作sql">python操作SQL</h2>
<p>pymsql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同</p>
<h4 id="一下载安装">一、下载安装</h4>
<pre><code>pip3 install pymysql
</code></pre><h4 id="二操作使用">二、操作使用</h4>
<p>1、执行SQL</p>
<pre><code> 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 # 创建连接
 6 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 7 # 创建游标
 8 cursor = conn.cursor()
 9   
10 # 执行SQL，并返回收影响行数
11 effect_row = cursor.execute(&quot;update hosts set host = '1.1.1.2'&quot;)
12   
13 # 执行SQL，并返回受影响行数
14 #effect_row = cursor.execute(&quot;update hosts set host = '1.1.1.2' where nid &gt; %s&quot;, (1,))
15   
16 # 执行SQL，并返回受影响行数
17 #effect_row = cursor.executemany(&quot;insert into hosts(host,color_id)values(%s,%s)&quot;, [(&quot;1.1.1.11&quot;,1),(&quot;1.1.1.11&quot;,2)])
18   
19   
20 # 提交，不然无法保存新建或者修改的数据
21 conn.commit()
22   
23 # 关闭游标
24 cursor.close()
25 # 关闭连接
26 conn.close()
</code></pre><p>2、获取新创建数据自增ID</p>
<pre><code> 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor()
 7 cursor.executemany(&quot;insert into hosts(host,color_id)values(%s,%s)&quot;, [(&quot;1.1.1.11&quot;,1),(&quot;1.1.1.11&quot;,2)])
 8 conn.commit()
 9 cursor.close()
10 conn.close()
11   
12 # 获取最新自增ID
13 new_id = cursor.lastrowid
</code></pre><p>3、获取查询数据</p>
<pre><code>1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6 cursor = conn.cursor()
 7 cursor.execute(&quot;select * from hosts&quot;)
 8   
 9 # 获取第一行数据
10 row_1 = cursor.fetchone()
11   
12 # 获取前n行数据
13 # row_2 = cursor.fetchmany(3)
14 # 获取所有数据
15 # row_3 = cursor.fetchall()
16   
17 conn.commit()
18 cursor.close()
19 conn.close()
</code></pre><p>注：在fetch数据时按照顺序进行，可以使用cursor.scroll(num,mode)来移动游标位置，如：<br>
cursor.scroll(1,mode='relative&rsquo;)  # 相对当前位置移动<br>
cursor.scroll(2,mode='absolute&rsquo;) # 相对绝对位置移动<br>
4、fetch数据类型关于默认获取的数据是元祖类型，如果想要或者字典类型的数据，即：</p>
<pre><code> 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 import pymysql
 4   
 5 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
 6   
 7 # 游标设置为字典类型
 8 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
 9 r = cursor.execute(&quot;call p1()&quot;)
10   
11 result = cursor.fetchone()
12   
13 conn.commit()
14 cursor.close()
15 conn.close()
</code></pre><h3 id="sqlalchemy">SQLAlchemy</h3>
<p>SQLAlchemy是Python编程语言下的一款ORM框架，该框架建立在数据库API之上，<br>
使用关系对象映射进行数据库操作，简言之便是：将对象转换成SQL，<br>
然后使用数据API执行SQL并获取执行结果。</p>
<h5 id="安装">安装：</h5>
<pre><code>pip3 install SQLAlchemy
</code></pre><p><img src="/my_technology_blog/010.png" alt=""></p>
<p>SQLAlchemy本身无法操作数据库，其必须以来pymsql等第三方插件，<br>
Dialect用于和数据API进行交流，根据配置文件的不同调用不同的数据库API，<br>
从而实现对数据库的操作，如：</p>
<pre><code>MySQL-Python
    mysql+mysqldb://&lt;user&gt;:&lt;password&gt;@&lt;host&gt;[:&lt;port&gt;]/&lt;dbname&gt;
   
pymysql
    mysql+pymysql://&lt;username&gt;:&lt;password&gt;@&lt;host&gt;/&lt;dbname&gt;[?&lt;options&gt;]
   
MySQL-Connector
    mysql+mysqlconnector://&lt;user&gt;:&lt;password&gt;@&lt;host&gt;[:&lt;port&gt;]/&lt;dbname&gt;
   
cx_Oracle
    oracle+cx_oracle://user:pass@host:port/dbname[?key=value&amp;key=value...]
</code></pre><h4 id="一内部处理">一、内部处理</h4>
<p>使用 Engine/ConnectionPooling/Dialect 进行数据库操作，Engine使用ConnectionPooling连接数据库<br>
然后再通过Dialect执行SQL语句。</p>
<pre><code>#!/usr/bin/env python
# -*- coding:utf-8 -*-
from sqlalchemy import create_engine
  
  
engine = create_engine(&quot;mysql+pymysql://root:123@127.0.0.1:3306/t1&quot;, max_overflow=5)
  
# 执行SQL
# cur = engine.execute(
#     &quot;INSERT INTO hosts (host, color_id) VALUES ('1.1.1.22', 3)&quot;
# )
  
# 新插入行自增ID
# cur.lastrowid
  
# 执行SQL
# cur = engine.execute(
#     &quot;INSERT INTO hosts (host, color_id) VALUES(%s, %s)&quot;,[('1.1.1.22', 3),('1.1.1.221', 3),]
# )
  
  
# 执行SQL
# cur = engine.execute(
#     &quot;INSERT INTO hosts (host, color_id) VALUES (%(host)s, %(color_id)s)&quot;,
#     host='1.1.1.99', color_id=3
# )
  
# 执行SQL
# cur = engine.execute('select * from hosts')
# 获取第一行数据
# cur.fetchone()
# 获取第n行数据
# cur.fetchmany(3)
# 获取所有数据
# cur.fetchall()

</code></pre><h4 id="二orm功能使用">二、ORM功能使用</h4>
<p>使用 ORM/Schema Type/SQL Expression Language/Engine/ConnectionPooling/Dialect<br>
所有组件对数据进行操作。根据类创建对象，对象转换成SQL，执行SQL。<br>
1、创建表</p>
<pre><code>1 #!/usr/bin/env python
2 # -*- coding:utf-8 -*-
3 from sqlalchemy.ext.declarative import declarative_base
4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
5 from sqlalchemy.orm import sessionmaker, relationship
6 from sqlalchemy import create_engine
7  
8 engine = create_engine(&quot;mysql+pymysql://root:123@127.0.0.1:3306/t1&quot;, max_overflow=5)
9  
10 Base = declarative_base()
11  
12 # 创建单表
13 class Users(Base):
14     __tablename__ = 'users'
15     id = Column(Integer, primary_key=True)
16     name = Column(String(32))
17     extra = Column(String(16))
18  
19     __table_args__ = (
20     UniqueConstraint('id', 'name', name='uix_id_name'),
21         Index('ix_id_name', 'name', 'extra'),
22     )
23  
24  
25 # 一对多
26 class Favor(Base):
27     __tablename__ = 'favor'
28     nid = Column(Integer, primary_key=True)
29     caption = Column(String(50), default='red', unique=True)
30  
31  
32 class Person(Base):
33     __tablename__ = 'person'
34     nid = Column(Integer, primary_key=True)
35     name = Column(String(32), index=True, nullable=True)
36     favor_id = Column(Integer, ForeignKey(&quot;favor.nid&quot;))
37  
38  
39 # 多对多
40 class Group(Base):
41     __tablename__ = 'group'
42     id = Column(Integer, primary_key=True)
43     name = Column(String(64), unique=True, nullable=False)
44     port = Column(Integer, default=22)
45  
46  
47 class Server(Base):
48     __tablename__ = 'server'
49  
50     id = Column(Integer, primary_key=True, autoincrement=True)
51     hostname = Column(String(64), unique=True, nullable=False)
52  
53  
54 class ServerToGroup(Base):
55     __tablename__ = 'servertogroup'
56     nid = Column(Integer, primary_key=True, autoincrement=True)
57     server_id = Column(Integer, ForeignKey('server.id'))
58     group_id = Column(Integer, ForeignKey('group.id'))
59  
60  
61 def init_db():
62     Base.metadata.create_all(engine)
63  
64  
65 def drop_db():
66     Base.metadata.drop_all(engine)
67 注：设置外检的另一种方式 ForeignKeyConstraint(['other_id'], ['othertable.other_id'])
</code></pre><p>2、操作表</p>
<pre><code> 1 #!/usr/bin/env python
 2 # -*- coding:utf-8 -*-
 3 from sqlalchemy.ext.declarative import declarative_base
 4 from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Index
 5 from sqlalchemy.orm import sessionmaker, relationship
 6 from sqlalchemy import create_engine
 7 
 8 engine = create_engine(&quot;mysql+pymysql://root:123@127.0.0.1:3306/t1&quot;, max_overflow=5)
 9 
10 Base = declarative_base()
11 
12 # 创建单表
13 class Users(Base):
14     __tablename__ = 'users'
15     id = Column(Integer, primary_key=True)
16     name = Column(String(32))
17     extra = Column(String(16))
18 
19     __table_args__ = (
20     UniqueConstraint('id', 'name', name='uix_id_name'),
21         Index('ix_id_name', 'name', 'extra'),
22     )
23 
24     def __repr__(self):
25         return &quot;%s-%s&quot; %(self.id, self.name)
26 
27 # 一对多
28 class Favor(Base):
29     __tablename__ = 'favor'
30     nid = Column(Integer, primary_key=True)
31     caption = Column(String(50), default='red', unique=True)
32 
33     def __repr__(self):
34         return &quot;%s-%s&quot; %(self.nid, self.caption)
35 
36 class Person(Base):
37     __tablename__ = 'person'
38     nid = Column(Integer, primary_key=True)
39     name = Column(String(32), index=True, nullable=True)
40     favor_id = Column(Integer, ForeignKey(&quot;favor.nid&quot;))
41     # 与生成表结构无关，仅用于查询方便
42     favor = relationship(&quot;Favor&quot;, backref='pers')
43 
44 # 多对多
45 class ServerToGroup(Base):
46     __tablename__ = 'servertogroup'
47     nid = Column(Integer, primary_key=True, autoincrement=True)
48     server_id = Column(Integer, ForeignKey('server.id'))
49     group_id = Column(Integer, ForeignKey('group.id'))
50     group = relationship(&quot;Group&quot;, backref='s2g')
51     server = relationship(&quot;Server&quot;, backref='s2g')
52 
53 class Group(Base):
54     __tablename__ = 'group'
55     id = Column(Integer, primary_key=True)
56     name = Column(String(64), unique=True, nullable=False)
57     port = Column(Integer, default=22)
58     # group = relationship('Group',secondary=ServerToGroup,backref='host_list')
59 
60 
61 class Server(Base):
62     __tablename__ = 'server'
63 
64     id = Column(Integer, primary_key=True, autoincrement=True)
65     hostname = Column(String(64), unique=True, nullable=False)
66 
67 
68 
69 
70 def init_db():
71     Base.metadata.create_all(engine)
72 
73 
74 def drop_db():
75     Base.metadata.drop_all(engine)
76 
77 
78 Session = sessionmaker(bind=engine)
79 session = Session()

</code></pre><h5 id="增">增</h5>
<pre><code>1 obj = Users(name=&quot;alex0&quot;, extra='sb')
2 session.add(obj)
3 session.add_all([
4     Users(name=&quot;alex1&quot;, extra='sb'),
5     Users(name=&quot;alex2&quot;, extra='sb'),
6 ])
7 session.commit()
</code></pre><h5 id="删">删</h5>
<pre><code>1 session.query(Users).filter(Users.id &gt; 2).delete()
2 session.commit()
</code></pre><h5 id="改">改</h5>
<pre><code>1 session.query(Users).filter(Users.id &gt; 2).update({&quot;name&quot; : &quot;099&quot;})
2 session.query(Users).filter(Users.id &gt; 2).update({Users.name: Users.name + &quot;099&quot;}, synchronize_session=False)
3 session.query(Users).filter(Users.id &gt; 2).update({&quot;num&quot;: Users.num + 1}, synchronize_session=&quot;evaluate&quot;)
4 session.commit()
</code></pre><h5 id="查">查</h5>
<pre><code>1 ret = session.query(Users).all()
2 ret = session.query(Users.name, Users.extra).all()
3 ret = session.query(Users).filter_by(name='alex').all()
4 ret = session.query(Users).filter_by(name='alex').first()
</code></pre>
</div>


    </main>

    
      
    
  </body>
</html>
